Excel BI - Excel Challenge 773

excel-challenges

excel-formulas

🔰 From the given symmetric triangles, find the numbers marked as X.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 773

Challenge Description

🔰 From the given symmetric triangles, find the numbers marked as X.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/773/773 Missing Numbers.xlsx"
input1 = read_excel(path, range = "A2:C3", col_names = FALSE) 
input2 = read_excel(path, range = "A5:E7", col_names = FALSE) 
input3 = read_excel(path, range = "A9:G12", col_names = FALSE) 
input4 = read_excel(path, range = "A14:M20", col_names = FALSE) 

test1 = read_excel(path, range = "O2:O2", col_names = FALSE) %>% pull() %>% as.character()
test2 = read_excel(path, range = "O5:O5", col_names = FALSE) %>% pull() %>% as.character()
test3 = read_excel(path, range = "O9:O9", col_names = FALSE) %>% pull()
test4 = read_excel(path, range = "O14:O14", col_names = FALSE) %>% pull()

find_missing_triangle_values <- function(df) {
  colnames(df) <- paste0("V", seq_len(ncol(df)))
  
  df %>%
    mutate(across(everything(), as.character)) %>%
    rowid_to_column() %>%
    pivot_longer(cols = starts_with("V"),
                 names_to = "col",
                 values_to = "value") %>%
    group_by(rowid) %>%
    mutate(left = lag(value, default = NA),
           right = lead(value, default = NA)) %>%
    ungroup() %>%
    filter(value == "X") %>%
    mutate(new_value = case_when(
      !is.na(left) & is.na(right) ~ as.numeric(left) + 1,
      is.na(left) & !is.na(right) ~ as.numeric(right) + 1,
      !is.na(left) & !is.na(right) & right == left ~ as.numeric(left) - 1,
      !is.na(left) & !is.na(right) & right != left ~ (as.numeric(left) + as.numeric(right)) / 2,
      is.na(left) & is.na(right) ~ 1
    )) %>%
    pull(new_value) %>%
    paste(collapse = ", ")
}

all.equal(find_missing_triangle_values(input1), test1, check.attributes = FALSE) # True
all.equal(find_missing_triangle_values(input2), test2, check.attributes = FALSE) # True
all.equal(find_missing_triangle_values(input3), test3, check.attributes = FALSE) # True
all.equal(find_missing_triangle_values(input4), test4, check.attributes = FALSE) # True

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level; Reshape the result into the workbook output format.
Strengths: The transformation is organized around the correct grouping level, which keeps the business logic clear.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The key move is solving the problem at the right grain before shaping the final output.

import pandas as pd

path = "700-799/773/773 Missing Numbers.xlsx"

input1 = pd.read_excel(path, header=None, usecols="A:C", skiprows=1, nrows=2)
input2 = pd.read_excel(path, header=None, usecols="A:E", skiprows=4, nrows=3)
input3 = pd.read_excel(path, header=None, usecols="A:G", skiprows=8, nrows=4)
input4 = pd.read_excel(path, header=None, usecols="A:M", skiprows=13, nrows=7)

test1 = str(pd.read_excel(path, header=None, usecols="O", skiprows=1, nrows=1).iloc[0, 0])
test2 = str(pd.read_excel(path, header=None, usecols="O", skiprows=4, nrows=1).iloc[0, 0])
test3 = pd.read_excel(path, header=None, usecols="O", skiprows=8, nrows=1).iloc[0, 0]
test4 = pd.read_excel(path, header=None, usecols="O", skiprows=13, nrows=1).iloc[0, 0]

def find_missing_triangle_values(df):
    df = df.copy().astype(str)
    res = []
    for row in df.values:
        for j, val in enumerate(row):
            if val == "X":
                l = row[j-1] if j > 0 else None
                r = row[j+1] if j < len(row)-1 else None
                try: l = float(l) if l not in [None, 'nan'] else None
                except: l = None
                try: r = float(r) if r not in [None, 'nan'] else None
                except: r = None
                if l is None and r is None: v = 1
                elif l is not None and r is None: v = l + 1
                elif l is None and r is not None: v = r + 1
                elif l == r: v = l - 1
                else: v = (l + r) / 2
                res.append(int(v) if v == int(v) else v)
    return ", ".join(map(str, res))

print(find_missing_triangle_values(input1) == test1)
print(find_missing_triangle_values(input2) == test2)
print(find_missing_triangle_values(input3) == test3)
print(find_missing_triangle_values(input4) == test4)

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.